package a_o2odata_deal.utils.zhibo_zf

import org.apache.spark.{SparkConf, SparkContext}
import org.apache.spark.sql.SQLContext

/**
  * @ Auther: o2o-rd-0008
  * @ Date:   2020/1/2 18:14
  * @ Param:  ${PARAM}
  * @ Description: 
  */
object handle_good {
  def main(args: Array[String]): Unit = {
    val conf = new SparkConf()
    conf.setAppName(s"${this.getClass.getSimpleName}")
    conf.set("spark.debug.maxToStringFields", "500")
    //conf.setMaster("local[*]")
    conf.set("es.nodes", "192.168.1.157")
    conf.set("es.port", "9200")
    conf.set("cluster.name", "O2OElastic")
    conf.set("spark.serializer", "org.apache.spark.serializer.KryoSerializer")
    conf.set("spark.sql.caseSensitive", "true")
    val sc = new SparkContext(conf)
    val sqlContext = new SQLContext(sc)
    sc.setLogLevel("WARN")
    sc.hadoopConfiguration.set("fs.s3a.access.key", "GAO7EO9FWKPJ8WFCQDME")
    sc.hadoopConfiguration.set("fs.s3a.secret.key", "LZ0xaHBSYKHaJ9ECDbX9f7zin79UZkXfGoNapRPL")
    //sc.hadoopConfiguration.set("fs.s3a.endpoint", "https://obs.cn-north-1.myhuaweicloud.com")

    for (months <- 1 to 1){
      //加载天猫，淘宝数据
      val tb_data = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/taobao/2020/${months}/zhibo_finally")
        .selectExpr("good_id","sellCount","salesAmount","province","city","district","regional_ID",
        "firstCategoryId","secondCategoryId","thirdCategoryId","fourthCategoryId","platformId","timeStamp",
        "is_showLive","is_selfLive","viewcount")
      val tm_data = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/tmall/2020/${months}/zhibo_finally")
        .selectExpr("good_id","sellCount","salesAmount","province","city","district","regional_ID",
          "firstCategoryId","secondCategoryId","thirdCategoryId","fourthCategoryId","platformId","timeStamp",
          "is_showLive","is_selfLive","viewcount")

      val source_data = tb_data.union(tm_data)
      source_data.createOrReplaceTempView("source_data")

      //计算每个区县每个三级行业下的总销量，销售额，商品条数
      sqlContext.sql(
        s"""
           |select
           |*,
           |sum(sellCount) over(partition by major_key) as sellCounts,
           |sum(salesAmount) over(partition by major_key) as salesAmounts,
           |count(good_id) over(partition by major_key) as goodCount
           |from
           |(select
           |*,
           |concat(concat(concat(province,city),district),thirdCategoryId) as major_key
           |from source_data)
         """.stripMargin).drop("sellCount","salesAmount").dropDuplicates("major_key")
        .withColumnRenamed("sellCounts","sellCount")
        .withColumnRenamed("salesAmounts","salesAmount")
        .createOrReplaceTempView("all_data")

      //将直播数据摘出来
      source_data.where("cast(is_showLive as boolean)=true").createOrReplaceTempView("zhibo_data")
      //计算每个区县每个三级行业下的总观看人数，直播商品数，直播总销量，直播总销售额
      sqlContext.sql(
        s"""
           |select
           |*,
           |sum(sellCount) over(partition by major_key) as live_sellCount,
           |sum(salesAmount) over(partition by major_key) as live_salesAmount,
           |sum(viewcount) over(partition by major_key) as viewCount,
           |count(good_id) over(partition by major_key) as live_goodCount
           |from
           |(select
           |*,
           |concat(concat(concat(province,city),district),thirdCategoryId) as major_key
           |from zhibo_data)
         """.stripMargin).dropDuplicates("major_key").createOrReplaceTempView("live_data")

      sqlContext.sql(
        s"""
           |select
           |t1.major_key,
           |t1.province,
           |t1.city,
           |t1.district,
           |t1.regional_ID,
           |t1.firstCategoryId,
           |t1.secondCategoryId,
           |t1.thirdCategoryId,
           |t1.fourthCategoryId,
           |t1.platformId,
           |t1.timeStamp,
           |t1.sellCount,
           |t1.salesAmount,
           |t1.goodCount,
           |case when t2.major_key is null then '0' else t2.live_sellCount end as live_sellCount,
           |case when t2.major_key is null then '0' else t2.live_salesAmount end as live_salesAmount,
           |case when t2.major_key is null then '0' else t2.viewCount end as viewCount,
           |case when t2.major_key is null then '0' else t2.live_goodCount end as live_goodCount
           |from all_data t1
           |left join
           |live_data t2
           |on t1.major_key=t2.major_key
         """.stripMargin).repartition(4).write.orc(s"s3a://o2o-dataproces-group/panzonghao/zhibo_zf/2020/${months}/good")

    }
  }

}





